﻿// =================================================================== 
// 项目说明
//====================================================================
// sushee @Copy Right 2019
// 文件： SmsMessagePoolRepository.cs
// 项目名称：斑马车务
// 创建时间：2019/9/19
// 负责人：sushee
// ===================================================================
using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using Dapper;
using YouWei.Infrastructure.Entity;
using YouWei.Infrastructure.Models;

namespace YouWei.Infrastructure.Repository
{
	/// <summary>
	///短信信息池 仓储层
	/// </summary>	
	public class SmsMessagePoolRepository:BaseRepository<SmsMessagePool>
	{		
    
        #region 构造

        public SmsMessagePoolRepository() : base(){}

        public SmsMessagePoolRepository(string connectionString) : base(connectionString){}

        public SmsMessagePoolRepository(DatabaseContext dbContext) : base(dbContext){}

        #endregion


        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
	    public List<SmsMessagePool> GetNeedToUpdateStateMsgs()
	    {
	        string szSql = @"SELECT 
	                        top 100 *
                        FROM SmsMessagePool m with(nolock)
                        WHERE 1=1
	                        AND m.IsJoinSendQueue=1 
	                        AND m.SmsCatSendID>0
                            AND m.SendStatus  IN(0,1)";
	        var list = Query(szSql);
	        return list;
	    }

	    public List<SmsMessagePool> GetGroupMsgs(int groupCount)
	    {
            string szSql = @"SELECT 
	                            top {0} *
                            FROM SmsMessagePool m
                            WHERE 1=1
	                        AND m.IsJoinSendQueue=0 
	                        ";
	        szSql = string.Format(szSql, groupCount);
            var list = Query(szSql);
            return list;
        }

        public List<SmsMessagePool> GetGroupMsgs(string carBrand,string carSerial,int groupCount)
        {
            //AND m.ReceiveMobileTelType IN(2,3),代表只查询联通、电信的数据
            string szSql = @"SELECT 
	                            top {0} *
                            FROM SmsMessagePool m
                            WHERE 1=1
	                        AND m.IsJoinSendQueue=0 
                            AND m.CarBrand='{1}'
                            AND m.CarSerial='{2}'
                            AND m.ReceiveMobileTelType IN(1,2,3)
	                        ";
            szSql = string.Format(szSql, groupCount,carBrand,carSerial);
            var list = Query(szSql);
            return list;
        }

        public int GetTimeRangeSendCountByMobile(string mobile,int startTime,int endTime)
	    {
            string szSql = @"SELECT 
		                    COUNT(*)
	                    FROM SmsMessagePool m
	                    WHERE 1=1
	                    AND	datepart(hh,m.SendDate)>=@StartTime
	                    AND datepart(hh,m.SendDate)<@EndTime
                        AND datepart(dd,m.SendDate)=DATEPART(dd,getdate())
                        AND IsJoinSendQueue=1
	                    AND SendMobile=@Mobile
	                        ";
            var nCount =ExecuteScalar<int>(szSql, new { Mobile = mobile, StartTime=startTime, EndTime = endTime });
            return nCount;
        }

        public int GetTimeRangeSendCountByMobile(string mobile, DateTime startTime, DateTime endTime)
        {
            string szSql = @"SELECT 
		                    COUNT(*)
	                    FROM SmsMessagePool m
	                    WHERE 1=1
	                    AND	m.SendDate>=@StartTime
	                    AND m.SendDate<@EndTime
                        AND datepart(dd,m.SendDate)=DATEPART(dd,getdate())
                        AND IsJoinSendQueue=1
	                    AND SendMobile=@Mobile
	                        ";
            var nCount = ExecuteScalar<int>(szSql, new { Mobile = mobile, StartTime = startTime, EndTime = endTime });
            return nCount;
        }

        public List<SmsStatisticsDto> GetStatisticsDaySend(string startTime, string endTime)
        {
            string szSql = @"select *
                            from (

                                    select 
	                            CarBrand as Col1
	                            ,CarSerial as Col2
	                            ,(
									select COUNT(*) from [SmsMessagePool] i 
									where i.IsJoinSendQueue=1
									AND i.CarBrand=o.CarBrand
									AND i.CarSerial=o.CarSerial
									AND SendDate>'{0}'
									AND SendDate<'{1}'
								) as Col3
                              FROM [SmsMessagePool] o
                              WHERE 1=1
                              and CarSerial IN
                              (
	                           select 
	                                distinct CarSerial
                                FROM SmsTask
                                where IsDeleted=0
                              )
                              GROUP BY CarBrand,CarSerial


							  union all 

							  select 
								'总合计' as Col1
								,'' as Col2
								,COUNT(*) from [SmsMessagePool] 
							where 1=1 
							AND IsJoinSendQueue=1
							and CarSerial IN
                              (
	                           select 
	                                distinct CarSerial
                                FROM SmsTask
                                where IsDeleted=0
                              )
							AND SendDate>'{0}'
							AND SendDate<'{1}'

                          ) aa
                          order by Col1,Col2
	                        ";
            szSql = string.Format(szSql, startTime, endTime);
            var list = Database.Connection.Query<SmsStatisticsDto>(szSql).ToList();
            return list;
        }
        public List<SmsStatisticsDto> GetStatisticsDaySendAll(string startTime, string endTime)
        {
            string szSql = @"select *
                            from (

                                    select 
	                            CarBrand as Col1
	                            ,CarSerial as Col2
	                            ,(
									select COUNT(*) from [SmsMessagePool] i 
									where i.IsJoinSendQueue=1
									AND i.CarBrand=o.CarBrand
									AND i.CarSerial=o.CarSerial
									AND SendDate>'{0}'
									AND SendDate<'{1}'
								) as Col3
                              FROM [SmsMessagePool] o
                              WHERE 1=1
                              and CarSerial IN
                              (
	                           select 
	                                distinct CarSerial
                                FROM SmsTask
                               
                              )
                              GROUP BY CarBrand,CarSerial


							  union all 

							  select 
								'总合计' as Col1
								,'' as Col2
								,COUNT(*) from [SmsMessagePool] 
							where 1=1 
							AND IsJoinSendQueue=1
							and CarSerial IN
                              (
	                           select 
	                                distinct CarSerial
                                FROM SmsTask
                                where IsDeleted=0
                              )
							AND SendDate>'{0}'
							AND SendDate<'{1}'

                          ) aa
                          order by Col1,Col2
	                        ";
            szSql = string.Format(szSql, startTime, endTime);
            var list = Database.Connection.Query<SmsStatisticsDto>(szSql).ToList();
            return list;
        }
        public List<SmsStatisticsDto> GetStatisticsRemain()
        {
            
            string szSql = @"select 
	                        CarBrand as Col1
	                        ,CarSerial as Col2
	                        ,(
		                        select 
			                        COUNT(*) 
		                        FROM SmsMessagePool
		                        WHERE IsJoinSendQueue=0
		                        AND CarBrand=t.CarBrand
		                        AND CarSerial=t.CarSerial
		                        --AND ReceiveMobileTelType IN(2,3)
	                        ) as Col3
                        FROM SmsTask t
                        where IsDeleted=0
                        GROUP BY CarBrand,CarSerial
                        ORDER BY CarBrand,CarSerial
	                        ";
            var list = Database.Connection.Query<SmsStatisticsDto>(szSql).ToList();
            return list;
        }

        public List<SmsStatisticsDto> GetStatisticsAllRemain()
        {
            string szSql = @"select 
	                            CarBrand as Col1
	                            ,CarSerial as Col2
	                            ,(
									select COUNT(*) from [SmsMessagePool] i 
									where i.IsJoinSendQueue=0 
									and i.CarBrand=o.CarBrand
									and i.CarSerial=o.CarSerial
								) as Col3
								,(
									select COUNT(*) from [SmsMessagePool] i 
									where 1=1
									and i.CarBrand=o.CarBrand
									and i.CarSerial=o.CarSerial
								) as Col4
                              FROM [SmsMessagePool] o 
							                               
                              GROUP BY CarBrand,CarSerial
                              order by CarBrand,CarSerial
	                        ";
            var list = Database.Connection.Query<SmsStatisticsDto>(szSql).ToList();
            return list;
        }
    }
}